﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public  class MoveRequestBLL

    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
     

            // strSQL = " insert into hrm_es_move_requests(emp_seq,approved_status,record_status,created_by,created_when)";
            //strSQL += " values(" + lblEmpSeq.Text + ",'" + SystemInfo ._ProcessRecordStatus + "','" + SystemInfo._ActiveRecordStatus + "',1," + DateTime.Today + ");" + "\n";
        

        public void Insert_hrm_es_move_requests(int emp_seq,string no_seq,int approve_seq, string approved_pos,string remarks, int created_by)
        {

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);
                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@approved_position", approved_pos);
                dbManager.AddParameters(2, "@remarks", remarks);
                dbManager.AddParameters(3, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(4, "@created_by", created_by);
                dbManager.AddParameters(5, "@approved_status", SystemInfo._ProcessRecordStatus);
                dbManager.AddParameters(6, "@approved_seq", approve_seq);
                dbManager.AddParameters(7, "@created_when", DateTime.Today);
                dbManager.AddParameters(8, "@no_seq", no_seq);


                string strSQL;
                strSQL = " insert into hrm_es_move_requests(approved_seq,emp_seq,approved_position,remarks,record_status,created_by,approved_status,created_when,no_seq)"
                 + " values(@approved_seq,@emp_seq,@approved_position,@remarks,@record_status,@created_by,@approved_status,@created_when,@no_seq)";


                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }

        }
        public void Update_hrm_es_move_requests(int req_seq,string no_seq,int approved_seq, string approved_position, string remarks, int updated_by)
        {
         



            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@approved_position", approved_position);
                dbManager.AddParameters(1, "@remarks", remarks);
                dbManager.AddParameters(2, "@updated_by", updated_by);
                dbManager.AddParameters(3, "@req_seq", req_seq);
                dbManager.AddParameters(4, "@approved_seq", approved_seq);    
                dbManager.AddParameters(5, "@updated_when", DateTime.Today);
                dbManager.AddParameters(6, "@no_seq", no_seq);
                string strSQL;
                strSQL = " update hrm_es_move_requests set "
                + " approved_position = approved_position , "
                + " remarks = @remarks , "
                + " approved_seq = @approved_seq , "
                + " updated_by = @updated_by , "
                + " updated_when = @updated_when ,"
                + " no_seq = @no_seq "
                + " where req_seq= @req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
  
            }
            catch
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Delete(int req_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@updated_by", UpdatedBy);
                dbManager.AddParameters(2, "@req_seq", req_seq);

                string sql;
                sql = " update hrm_es_move_requests set "
                + " record_status=@record_status"
                + ", updated_by = @updated_by"
                + " where req_seq = @req_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
  ;
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


    
        public DataTable select(int emp_seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@ApproveRecord", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(2, "@EmpSeq", emp_seq);

                string strSQL;
                strSQL = "SELECT r.* "
                        + ", CONCAT(np2.PREFIX_NAME, e2.FIRST_NAME, '  ', e2.LAST_NAME) AS APPROVED_NAME"
                        + ", s.RECORD_STATUS_NAME AS APP_STAT_NAME"
                    + " FROM HRM_ES_MOVE_REQUESTS r"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON r.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e2 ON r.APPROVED_SEQ = e2.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np2 ON e2.NAME_PREFIX_SEQ = np2.NAME_PREFIX_SEQ"
                    + " LEFT JOIN MAS_RECORD_STATUSES s ON r.APPROVED_STATUS = s.RECORD_STATUS"
                    + " WHERE r.RECORD_STATUS = @ActiveRecord"
                        + " AND r.EMP_SEQ = @EmpSeq";
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable listMoveRequestMaxDate(int year, string emp_type_seq, string pos_type_seq, string class_seq, string edu_qual_seq, string edu_major_seq, string edu_ins_seq, string old_job_site, string request_job_site)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@ApproveRecord", SystemInfo._ApproveRecordStatus);

                string strSQL;
                strSQL = "SELECT DISTINCT CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME"
                        + ", rd.REQ_DTLS_SEQ, rd.SEQ, rd.REASON, p.POS_NAME, js.JOB_SITE_NAME AS OLD_JOB_SITE"
                        + ", js2.JOB_SITE_NAME AS REQUEST_JOB_SITE, r.EMP_SEQ"
                    + " FROM HRM_ES_MOVE_REQUESTS r"
                        + " LEFT JOIN HRM_ES_MOVE_REQUEST_DTLS rd ON r.REQ_SEQ = rd.REQ_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON r.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_EDUCATIONS ee ON e.EMP_SEQ = ee.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS fp ON p.FIELD_POS_SEQ = fp.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js ON p.JOB_SITE_SEQ = js.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES js2 ON rd.JOB_SITE_SEQ = js2.JOB_SITE_SEQ"
                    + " WHERE r.RECORD_STATUS = @ApproveRecord"
                    + " AND e.RECORD_STATUS = @ActiveRecord"
                    + " AND r.APPROVED_DATE = (select max(approved_date)"
                                        + " from hrm_es_move_requests"
                                        + " where record_status = @ApproveRecord"
                                        + " group by emp_seq)";

                if (emp_type_seq != "")
                    strSQL += " and e.emp_type_seq = " + emp_type_seq;
                if (pos_type_seq != "")
                    strSQL += " and fp.pos_type_seq = " + pos_type_seq;
                if (class_seq != "")
                    strSQL += " and e.class_seq = " + class_seq;
                if (edu_qual_seq != "")
                    strSQL += " and ee.edu_qual_seq in (" + edu_qual_seq + ")";
                if (edu_major_seq != "")
                    strSQL += " and ee.edu_major_seq in (" + edu_major_seq + ")";
                if (edu_ins_seq != "")
                    strSQL += " and ee.edu_ins_seq in (" + edu_ins_seq + ")";
                if (old_job_site != "")
                    strSQL += " and  p.job_site_seq = " + old_job_site;
                if (request_job_site != "")
                    strSQL += " and rd.job_site_seq = " + request_job_site;

                //sql += " group by  req_dtls_seq, approved_date,e.first_name, e.last_name, p.pos_name,old_job_site, request_job_site ";
                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                return null;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public int get_req_seq(int emp_seq)
        {
            string sql;
            sql = "select max(req_seq) from hrm_es_move_requests where emp_seq=" + emp_seq;
            try
            {
                object obj;
                dbManager.Open();
                obj = dbManager.ExecuteScalar(CommandType.Text, sql);
               
                return (int)obj + 1;
            }
            catch
            {
                return 1;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int getSeq(int emp_seq)
        {
            string sql;
            sql = "select max(seq) from hrm_es_move_request_dtls where emp_seq=" + emp_seq ;
            try
            {
                object obj;
                dbManager.Open();
                obj = dbManager.ExecuteScalar(CommandType.Text, sql);
             
                return (int)obj + 1;
                
            }
            catch (Exception Ex)
            {
                return 1;
                // throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }



    }
}
